package store.dao;

import store.po.Restock;
import store.po.Sale;
import store.po.SaleItem;
import store.utils.Pager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SaleDAO {
    public List<Sale> findSales(String startDate,String endDate, Pager pager) throws Exception {
        List<Sale> list=new ArrayList<>();
        Connection con= null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con= DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD);
            ps=con.prepareStatement("select count(id) as total from t_sale where trade_time between ? and ?");
            ps.setString(1,startDate);
            ps.setString(2,endDate);
            rs=ps.executeQuery();
            if(rs.next()){
                pager.setTotal(rs.getInt("total"));
            }
            ps=con.prepareStatement("select * from t_sale where trade_time between ? and ? limit ?,?");
            ps.setString(1,startDate);
            ps.setString(2,endDate);
            ps.setInt(3,(pager.getCurrentPage()-1)*pager.getPageSize());
            ps.setInt(4,pager.getPageSize());
            rs=ps.executeQuery();
            while (rs.next()){
                Sale s=new Sale();
                s.setId(rs.getInt("id"));
                s.setTradeTime(rs.getString("trade_time"));
                list.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库异常:"+e.getMessage());
        }finally {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(con!=null) con.close();
        }
        return list;
    }


    public void save(Sale s) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs=null;
        try {
            Class.forName(DB.JDBC_DRIVER);
            con = DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER, DB.JDBC_PASSWORD);
            con.setAutoCommit(false);
            ps = con.prepareStatement("insert into t_sale value (null ,?)", Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, s.getTradeTime());
            ps.executeUpdate();
            rs=ps.getGeneratedKeys();
            if(rs.next()){//订单生成后添加订单项
                StringBuilder sb=new StringBuilder();
                int sale_id=rs.getInt(1);
                s.getItems().forEach(saleItem ->{
                    sb.append("insert into t_sale_item value("+sale_id+","+saleItem.getGoodsId()+","+saleItem.getCount()+"); ");
                });

                //修改库存
                s.getItems().forEach(saleItem -> {
                    sb.append("update t_goods set stock=(stock- "+saleItem.getCount()+") where id="+saleItem.getGoodsId()+" ; ");
                });
                ps=con.prepareStatement(sb.toString());
                ps.executeUpdate();
            }
            con.commit();
        } catch (Exception e) {
            con.rollback();
            e.printStackTrace();
            throw new Exception("数据库异常:" + e.getMessage());
        } finally {
            if(rs!=null) rs.close();
            if (ps != null) ps.close();
            if (con != null) con.close();
        }
    }
}
